package reflect.engine;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import object.model.MySQL_TableDDL;
import object.model.MySQL_TableDesc;
import object.model.TableType;
import system.Sys_Config;
import system.mysql.schema.base.models.Proc;
import system.mysql.schema.information.models.COLUMNS;
import system.mysql.schema.information.models.REFERENTIAL_CONSTRAINTS;
import system.mysql.schema.information.models.STATISTICS;
import system.mysql.schema.information.models.TABLES;
import system.mysql.schema.information.models.TRIGGERS;
import system.mysql.schema.information.models.VIEWS;
import system.queryer.MySQL_Queryer;

public class DataBaseStructInfoer {

	private MySQL_Queryer mysqlQ = new MySQL_Queryer();
	
	public List<MySQL_TableDesc> getTableDesc(String tableName){
		String SQL = "DESCRIBE `" + tableName + "`";
		List<MySQL_TableDesc> descList = this.mysqlQ.toFind(SQL, new MySQL_TableDesc(), null, null);
		return descList;
	}
	
	public List<String> getDBTableNames(){
		String SQL = "SHOW TABLES";
		List li = mysqlQ.toFind(SQL, null, null, null);
		List<String> strL = new ArrayList<String>();
		for(int i=0;i<li.size();i++){
			String name = ((Map)li.get(i)).get(Sys_Config.DB_SHOWTABLE_COLLUMNAME).toString();
			strL.add(name);
		}
		return strL;
	}
	
	public List<TABLES> getAllTables(){
		String SQL = "SELECT * from information_schema.`TABLES` WHERE TABLE_SCHEMA = '"+Sys_Config.DB_NAME+"'";
		List<TABLES> tbList = this.mysqlQ.toFind(SQL, new TABLES(), null, null);
		return tbList;
	}
	
	public List<VIEWS> getViews(){
		String SQL = "SELECT * from information_schema.`VIEWS` WHERE TABLE_SCHEMA = '"+Sys_Config.DB_NAME+"'";
		List<VIEWS> vList = this.mysqlQ.toFind(SQL, new VIEWS(), null, null);
		return vList;
	}
	
	public List<TABLES> getBaseTables(){
		String SQL = "SELECT * from information_schema.`TABLES` WHERE TABLE_SCHEMA = '"+Sys_Config.DB_NAME+"' AND TABLE_TYPE = 'BASE TABLE'";
		List<TABLES> tbList = this.mysqlQ.toFind(SQL, new TABLES(), null, null);
		return tbList;
	}
	
	public List<TRIGGERS> getTriggers(){
		String SQL = "SELECT * FROM information_schema.`TRIGGERS` WHERE TRIGGER_SCHEMA = '"+Sys_Config.DB_NAME+"';";
		List<TRIGGERS> trigList = this.mysqlQ.toFind(SQL, new TRIGGERS(), null, null);
		return trigList;
	}
	
	public List<Proc> getProcedures(){
		String SQL = "SELECT * FROM mysql.`proc` WHERE db = '"+Sys_Config.DB_NAME+"';";
		List<Proc> procList = this.mysqlQ.toFind(SQL, new Proc(), null, null);
		return procList;
	}
	
	public List<COLUMNS> getColumns(TABLES tb){
		String SQL = "SELECT * FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = '"+ tb.getTABLE_SCHEMA() +"' AND TABLE_NAME = '"+tb.getTABLE_NAME()+"';";
		List<COLUMNS> colList = this.mysqlQ.toFind(SQL, new COLUMNS(), null, null);
		return colList;
	}
	
	public List<STATISTICS> getStatistics(TABLES tb){
		String SQL = "SELECT * FROM information_schema.`STATISTICS` WHERE TABLE_SCHEMA = '" + tb.getTABLE_SCHEMA() + "' AND TABLE_NAME = '" + tb.getTABLE_NAME() + "' AND INDEX_NAME != 'PRIMARY'";
		List<STATISTICS> staList = this.mysqlQ.toFind(SQL, new STATISTICS(), null, null);
		return staList;
	}
	
	public List<REFERENTIAL_CONSTRAINTS> getRefConstrains(TABLES tb){
		String SQL = "SELECT * FROM information_schema.`REFERENTIAL_CONSTRAINTS` WHERE CONSTRAINT_SCHEMA = '"+tb.getTABLE_SCHEMA()+"' AND TABLE_NAME = '" +tb.getTABLE_NAME()+"'";
		List<REFERENTIAL_CONSTRAINTS> refConList = this.mysqlQ.toFind(SQL, new REFERENTIAL_CONSTRAINTS(), null, null);
		return refConList;
	}
	
	public TABLES getSingleTable(String name){
		String SQL = "SELECT * from information_schema.`TABLES` WHERE TABLE_SCHEMA = '"+Sys_Config.DB_NAME+"' AND TABLE_TYPE = 'BASE TABLE' TABLE_NAME = '"+name+"'" ;
		TABLES tb = (TABLES) this.mysqlQ.toFind(SQL, new TABLES(), null, null).get(0);
		return tb;		
	}
	
	public static void main(String[] args){
		new DataBaseStructInfoer().getSingleTable("");
	}
	
	
	public MySQL_TableDDL getTableDDL(TABLES tb, TableType type){
		String SQL = "SHOW CREATE "+type.toString()+" `"+tb.getTABLE_SCHEMA()+"`.`"+tb.getTABLE_NAME()+"`";
		List<Map<String, Object>> tbDDL = this.mysqlQ.toFind(SQL, null, null, null);
		Map<String, Object> ddl = tbDDL.get(0);
		MySQL_TableDDL d = new MySQL_TableDDL((String) ddl.get("Table"), type);
		d.setCreateTable((String) ddl.get("Create Table"));
		return d;
	}
	
	public MySQL_TableDDL getTableDDL(String name, TableType type){
		String SQL = "SHOW CREATE "+type.toString()+" "+name;
		String ddl = null;
		List<Map<String, Object>> li = mysqlQ.toFind(SQL, null, null, null);
		if(li!=null && li.size()>0){
			String idr = type.name().toLowerCase();
			idr = idr.substring(0, 1).toUpperCase() + idr.substring(1);
			ddl = li.get(0).get("Create "+idr).toString();
		}
		MySQL_TableDDL tbDDL = new MySQL_TableDDL(name, type);
		tbDDL.setCreateTable(ddl);
		return tbDDL;
	}
	
} 
